<?php

date_default_timezone_set('UTC');

// Get the command line parameters
$options = getopt("f:s:h:u:p:d:");
if(!file_exists($options['f'])) {
	die('File not found');
} else if(!isset($options['s'])) {
	die('Server name not set');
} else if(!isset($options['h'])) {
	die('Host not set');
} else if(!isset($options['u'])) {
	die('User not set');
} else if(!isset($options['p'])) {
	$options['p'] = "";
} else if(!isset($options['d'])) {
	die('Database not set');
}

echo "Starting...\n";

// Connect to the database
$con = mysql_connect($options['h'], $options['u'], $options['p']);
if (!$con){ die('Could not connect: ' . mysql_error()); }
mysql_select_db($options['d']);

// Test to see if the file is already loaded
if(is_file_loaded($options['f']))
{
	die('File already loaded');
}

// Read in the file
$lines = explode("\n", file_get_contents($options['f']));
$server_id = get_dimension_value("server", $options['s']);
$filename_id = get_dimension_value("file", $options['f']);

// Insert all of the lines of the log file into the database
for($i = 0; $i <sizeof($lines); $i++){
	$line = $lines[$i];
	if(strlen($line) > 0)
	{
		$par = format_log_line($line);
		$par['accessed'] = date( 'Y-m-d H:i:s', strtotime($par['date'] . ":" . $par['time'] . " " . $par['timezone']));
		foreach ($par as $key => $value)
		{
			if(!($key == "bytes" || $key == "status" || $key == "accessed" || $key == "active"))
			{
				$par[$key] = get_dimension_value($key, $value);
			}
		}
		
		$par['file'] = $filename_id;
		$par['server'] = $server_id;
		mysql_query(generate_log_insert_query($par));
	}
}

echo "Done.\n";

// Test if the file was already loaded into the database.
function is_file_loaded($filename)
{
	$query = "SELECT COUNT(*) NUM FROM `dim_file` WHERE `file` LIKE '$filename'";
	$result = mysql_query($query);
	$row = mysql_fetch_assoc($result);
	if($row['NUM'] > 0)
	{
		return TRUE;
	}
	else
	{
		return FALSE;
	}
}

// Generates the insert query for the fact table
function generate_log_insert_query($data)
{
	$names = "";
	$vals = "";
	foreach ($data as $key => $value)
	{
		$names .= "`$key`,";
		$vals .= "'$value',";
	}
	
	$names = substr($names, 0, -1);
	$vals = substr($vals, 0, -1);
	return "INSERT INTO `log_warehouse` ($names) VALUES ($vals);";
}

// Gets the dimension value 
function get_dimension_value($field, $value){
	$query = "SELECT dim.`id` FROM `dim_" . $field . "` dim WHERE dim.`" . $field . "` = '" . $value . "'";
	$result = mysql_query($query);
	$val = array();
	while($row = mysql_fetch_assoc($result)){
		$val[] = $row;
	}
	
	if(sizeof($val) == 0){
		// Insert the new value and return the id
		$query = "INSERT INTO `dim_" . $field . "` (`" . $field . "`) VALUES ('" . $value . "')";
		$result = mysql_query($query);
		$id = mysql_insert_id();
		return $id;
	}
	else {
		// Return the existing value
		return $val[0]['id'];
	}
}

// Parses a line of the log file
function format_log_line($line)
{
	preg_match("/^(\S+) (\S+) (\S+) \[([^:]+):(\d+:\d+:\d+) ([^\]]+)\] \"(\S+) (.*?) (\S+)\" (\S+) (\S+) \"?(.*?)\"? \"(.*?)\"$/", $line, $matched);
	if(isset($matched[0]))
	{
		$output = array(); // make an array to store the lin info in
		$output['ip'] = $matched[1];
		$output['identity'] = $matched[2];
		$output['user'] = $matched[2];
		$output['date'] = $matched[4];
		$output['time'] = $matched[5];
		$output['timezone'] = $matched[6];
		$output['method'] = $matched[7];
		$output['path'] = $matched[8];
		$output['protocol'] = $matched[9];
		$output['status'] = $matched[10];
		$output['bytes'] = $matched[11];
		$output['referer'] = $matched[12];
		$output['agent'] = $matched[13];
		$output['active'] = 1;
		return $output;
	}
	else
	{
		return FALSE;
	}
}
